import common
import unittest

custtable = (('Anonymous',1.9,None,None), \
    ('toolco',1.5,(46,),None), \
    ('widget',1.2,(48,),None), \
    ('misty',1.4,(None,),('misting',)) \
    )

class test_insert(common.TestCase):

    tables_to_drop = ('partlist','partcust')
    
    def test_insert_many(self):

        c = self.c
        
        c.execute("create table partlist (part varchar2(62), partno integer, quantity integer, price number(10,2)) tablespace users")

        file = open("namegen.out","r")

        v = []

        for l in file.readlines():

            (part, partno, quantity, price) = l.strip().split(',')

            v.append((part, partno, quantity, price))

        file.close()

        c.executemany("insert into partlist (part, partno, quantity, price) values (:1,:2,:3,:4)", v)

        self.db.commit()

        c.execute("select part, partno, quantity, price from partlist order by partno")

        results = c.fetchall()

        c.execute("create table partcust (customer varchar2(32), partno integer, price number(10,2)) tablespace users")

        for r in results:
            (part, partno, quantity, price) = r
    
            for (customer,factor,baseparts,pattern) in custtable:
                cfactor =  0
                if baseparts is not None:
                    for p in baseparts:
                        if int(partno / 1000) == p: cfactor = factor
                else:
                    cfactor = factor
                if pattern is not None:
                    for pat in pattern:
                        if part and part.find(pat) >= 0:
                            cfactor = factor
                if price is not None:
                    cprice = float("%8.2f" % (price * cfactor))
                else:
                    cprice = 0.0
                if (cprice > 0.0):
                    c.execute("insert into partcust (customer, partno, price) values (:1,:2,:3)", customer, partno, cprice)

        self.db.commit()

    def test_insert(self):

        c = self.c

        c.execute('select * from test')

        old_len = len(c.fetchall())

        c.execute('insert into test values (:1, :2, NULL)',
                  (('Fred Flintstone', 'Mavra Chang'), (4,5)))

        c.execute('select * from test')

        r = c.fetchall()

        self.assertEqual(len(r),old_len+2)

        self.assertEqual(
            r[-2:],
            [['Fred Flintstone', 4, ''], ['Mavra Chang', 5, '']]
            )

if __name__ == '__main__':
        unittest.main()
